﻿Imports System.Data
Imports System.Data.SqlClient
Imports System.Configuration


''' <summary>
''' 
''' </summary>
Public Class MssqlHelper
    ''' 
    Private myConnection As SqlConnection = Nothing
    Private ReadOnly RETURNVALUE As String = "RETURNVALUE"
    Private ReadOnly SQLCONNECTSTR As String = "server=192.168.111.110;uid=sa;pwd=adminstrator;database=MediacnvNet"

    ''' <summary>
    ''' 
    ''' </summary>
    Private Sub Open()
        ' 
        If myConnection Is Nothing Then
            'myConnection = new SqlConnection(ConfigurationManager.ConnectionStrings["ConStr"].ConnectionString);
            myConnection = New SqlConnection(SQLCONNECTSTR)
        End If
        If myConnection.State = ConnectionState.Closed Then
            Try
                '''
                myConnection.Open()
            Catch ex As Exception
                SystemError.SystemLog(ex.Message)
                '''     
            Finally
            End Try
        End If
    End Sub

    ''' <summary>
    ''' 
    ''' </summary>
    Public Sub Close()
        '''
        If myConnection IsNot Nothing Then
            '''
            If myConnection.State = ConnectionState.Open Then
                myConnection.Close()
            End If
        End If
    End Sub

    ''' <summary>
    ''' 
    ''' </summary>
    Public Sub Dispose()
        ' 
        If myConnection IsNot Nothing Then
            myConnection.Dispose()
            myConnection = Nothing
        End If
    End Sub

    ''' <summary>
    ''' 
    ''' </summary>
    ''' <param name="procName"></param>
    ''' <returns></returns>
    Public Function RunProc(ByVal procName As String) As Integer
        Dim cmd As SqlCommand = CreateProcCommand(procName, Nothing)
        Try
            '''
            cmd.ExecuteNonQuery()
        Catch ex As Exception
            '''
            SystemError.SystemLog(ex.Message)
        Finally
            '''
            Close()
        End Try

        '''
        Return CInt(cmd.Parameters(RETURNVALUE).Value)
    End Function
    ''' <summary>
    ''' 
    ''' </summary>
    ''' <param name="procName"></param>
    ''' <param name="prams"></param>
    ''' <returns></returns>
    Public Function RunProc(ByVal procName As String, ByVal prams As SqlParameter()) As Integer
        Dim cmd As SqlCommand = CreateProcCommand(procName, prams)
        Try
            '''
            cmd.ExecuteNonQuery()
        Catch ex As Exception
            '''
            SystemError.SystemLog(ex.Message)
        Finally
            '''
            Close()
        End Try

        '''
        Return CInt(cmd.Parameters(RETURNVALUE).Value)
    End Function

    ''' <summary>
    ''' 
    ''' </summary>
    ''' <param name="procName"></param>
    ''' <param name="dataReader"></param>
    Public Sub RunProc(ByVal procName As String, ByRef dataReader As SqlDataReader)
        '''
        Dim cmd As SqlCommand = CreateProcCommand(procName, Nothing)

        Try
            '''
            dataReader = cmd.ExecuteReader(CommandBehavior.CloseConnection)
        Catch ex As Exception
            dataReader = Nothing
            '''
            SystemError.SystemLog(ex.Message)
        End Try
    End Sub

    ''' <summary>
    ''' 
    ''' </summary>
    ''' <param name="procName"></param>
    ''' <param name="prams"></param>
    ''' <param name="dataSet"></param>
    Public Sub RunProc(ByVal procName As String, ByVal prams As SqlParameter(), ByRef dataReader As SqlDataReader)
        '''
        Dim cmd As SqlCommand = CreateProcCommand(procName, prams)

        Try
            '''
            dataReader = cmd.ExecuteReader(CommandBehavior.CloseConnection)
        Catch ex As Exception
            dataReader = Nothing
            '''
            SystemError.SystemLog(ex.Message)
        End Try
    End Sub


    ''' <summary>
    ''' 
    ''' </summary>
    ''' <param name="procName"></param>
    ''' <param name="prams"></param>
    ''' <param name="dataSet"></param>
    Public Sub RunProc(ByVal procName As String, ByRef dataSet As DataSet, Optional ByVal prams As SqlParameter() = Nothing)
        If dataSet Is Nothing Then
            dataSet = New DataSet()
        End If
        '''
        Dim da As SqlDataAdapter = CreateProcDataAdapter(procName, prams)

        Try
            '''
            da.Fill(dataSet)
        Catch ex As Exception
            '''
            SystemError.SystemLog(ex.Message)
        Finally
            '''
            Close()
        End Try
    End Sub


    ''' <summary>
    ''' 
    ''' </summary>
    ''' <param name="procName"></param>
    ''' <param name="prams"></param>
    ''' <param name="DataTable"></param>
    Public Sub RunProc(ByVal procName As String, ByRef dataTable As DataTable, Optional ByVal prams As SqlParameter() = Nothing)
        If dataTable Is Nothing Then
            dataTable = New DataTable()
        End If
        '''
        Dim da As SqlDataAdapter = CreateProcDataAdapter(procName, prams)

        Try
            '''
            da.Fill(dataTable)
        Catch ex As Exception
            '''
            SystemError.SystemLog(ex.Message)
        Finally
            '''
            Close()
        End Try
    End Sub

    ''' <summary>
    ''' 
    ''' </summary>
    ''' <param name="procName"></param>
    ''' <param name="prams"></param>
    ''' <param name="DataTable"></param>
    Public Sub RunProc2(ByVal procName As String, ByRef dataTable As DataTable, Optional ByVal item As DB.MssqlUtilities.STC_Item() = Nothing, Optional ByVal prams As SqlParameter() = Nothing)
        If dataTable Is Nothing Then
            dataTable = New DataTable()
        End If
        If item IsNot Nothing Then
            For i As Integer = 0 To item.Length - 1
                procName += item(i).SqlKey
                'tbl.Sql += item[i].ItemId + "+'%'";
                procName += item(i).ItemId
            Next
        End If
        '''
        Dim da As SqlDataAdapter = CreateProcDataAdapter(procName, prams)

        Try
            '''
            da.Fill(dataTable)
        Catch ex As Exception
            '''
            SystemError.SystemLog(ex.Message)
        Finally
            '''
            Close()
        End Try
    End Sub

    ''' <summary>
    ''' 
    ''' </summary>
    ''' <param name="cmdText">SQL</param>
    ''' <returns></returns>
    Public Function RunSQL(ByVal cmdText As String) As Integer
        Dim cmd As SqlCommand = CreateSQLCommand(cmdText, Nothing)
        Try
            '''
            cmd.ExecuteNonQuery()
        Catch ex As Exception
            '''
            SystemError.SystemLog(ex.Message)
        Finally
            '''
            Close()
        End Try

        '''
        Return CInt(cmd.Parameters(RETURNVALUE).Value)
    End Function
    ' <summary>
    ''' 
    ''' </summary>
    ''' <param name="cmdText">SQL</param>
    ''' <param name="prams">SQL</param>
    ''' <returns></returns>
    Public Function RunSQL(ByVal cmdText As String, ByVal prams As SqlParameter()) As Integer
        Dim cmd As SqlCommand = CreateSQLCommand(cmdText, prams)
        Try
            '''
            cmd.ExecuteNonQuery()
        Catch ex As Exception
            '''
            SystemError.SystemLog(ex.Message)
        Finally
            '''
            Close()
        End Try

        '''
        Return CInt(cmd.Parameters(RETURNVALUE).Value)
    End Function

    ''' <summary>
    ''' SQL
    ''' </summary>
    ''' <param name="cmdText">SQL</param> 
    ''' <param name="dataReader">DataReader</param>
    Public Sub RunSQL(ByVal cmdText As String, ByRef dataReader As SqlDataReader)
        '''
        Dim cmd As SqlCommand = CreateSQLCommand(cmdText, Nothing)

        Try
            '''
            dataReader = cmd.ExecuteReader(CommandBehavior.CloseConnection)
        Catch ex As Exception
            dataReader = Nothing
            '''
            SystemError.SystemLog(ex.Message)
        End Try
    End Sub

    ''' <summary>
    ''' SQL
    ''' </summary>
    ''' <param name="cmdText">SQL</param>
    ''' <param name="prams">SQL</param>
    ''' <param name="dataReader">DataReader</param>
    Public Sub RunSQL(ByVal cmdText As String, ByVal prams As SqlParameter(), ByRef dataReader As SqlDataReader)
        '''Command
        Dim cmd As SqlCommand = CreateSQLCommand(cmdText, prams)

        Try
            '''
            dataReader = cmd.ExecuteReader(CommandBehavior.CloseConnection)
        Catch ex As Exception
            dataReader = Nothing
            '''
            SystemError.SystemLog(ex.Message)
        End Try
    End Sub

    ''' <summary>
    ''' 
    ''' </summary>
    ''' <param name="cmdText">SQL</param>
    ''' <param name="dataSet">DataSet</param>
    Public Sub RunSQL(ByVal cmdText As String, ByRef dataSet As DataSet)
        If dataSet Is Nothing Then
            dataSet = New DataSet()
        End If
        '''SqlDataAdapter
        Dim da As SqlDataAdapter = CreateSQLDataAdapter(cmdText, Nothing)

        Try
            '''
            da.Fill(dataSet)
        Catch ex As Exception
            '''
            SystemError.SystemLog(ex.Message)
        Finally
            '''
            Close()
        End Try
    End Sub

    ''' <summary>
    ''' 
    ''' </summary>
    ''' <param name="cmdText">SQL</param>
    ''' <param name="prams">SQL</param>
    ''' <param name="dataSet">DataSet</param>
    Public Sub RunSQL(ByVal cmdText As String, ByVal prams As SqlParameter(), ByRef dataSet As DataSet)
        If dataSet Is Nothing Then
            dataSet = New DataSet()
        End If
        '''SqlDataAdapter
        Dim da As SqlDataAdapter = CreateProcDataAdapter(cmdText, prams)

        Try
            '''
            da.Fill(dataSet)
        Catch ex As Exception
            '''
            SystemError.SystemLog(ex.Message)
        Finally
            '''
            Close()
        End Try
    End Sub

    ''' <summary>
    ''' 
    ''' </summary>
    ''' <param name="procName"></param>
    ''' <param name="prams"></param>
    ''' <returns></returns>
    Private Function CreateProcCommand(ByVal procName As String, ByVal prams As SqlParameter()) As SqlCommand
        '''
        Open()

        '''
        Dim cmd As New SqlCommand(procName, myConnection)
        cmd.CommandType = CommandType.StoredProcedure

        '''
        If prams IsNot Nothing Then
            For Each parameter As SqlParameter In prams
                cmd.Parameters.Add(parameter)
            Next
        End If

        '''ReturnValue
        cmd.Parameters.Add(New SqlParameter(RETURNVALUE, SqlDbType.Int, 4, ParameterDirection.ReturnValue, False, 0, _
         0, String.Empty, DataRowVersion.[Default], Nothing))

        '''SqlCommand
        Return cmd
    End Function

    Private Function CreateSQLCommand(ByVal cmdText As String, ByVal prams As SqlParameter()) As SqlCommand
        '''
        Open()

        '''
        Dim cmd As New SqlCommand(cmdText, myConnection)

        '''
        If prams IsNot Nothing Then
            For Each parameter As SqlParameter In prams
                cmd.Parameters.Add(parameter)
            Next
        End If

        '''
        cmd.Parameters.Add(New SqlParameter(RETURNVALUE, SqlDbType.Int, 4, ParameterDirection.ReturnValue, False, 0, _
         0, String.Empty, DataRowVersion.[Default], Nothing))

        '''
        Return cmd
    End Function

    Private Function CreateProcDataAdapter(ByVal procName As String, ByVal prams As SqlParameter()) As SqlDataAdapter
        '''
        Open()

        '''
        Dim da As New SqlDataAdapter(procName, myConnection)
        'da.SelectCommand.CommandType = CommandType.StoredProcedure; //2013/02/26 shin

        '''
        If prams IsNot Nothing Then
            For Each parameter As SqlParameter In prams
                da.SelectCommand.Parameters.Add(parameter)
            Next
        End If

        '''
        da.SelectCommand.Parameters.Add(New SqlParameter(RETURNVALUE, SqlDbType.Int, 4, ParameterDirection.ReturnValue, False, 0, _
         0, String.Empty, DataRowVersion.[Default], Nothing))

        Return da
    End Function

    Private Function CreateSQLDataAdapter(ByVal cmdText As String, ByVal prams As SqlParameter()) As SqlDataAdapter
        Open()

        Dim da As New SqlDataAdapter(cmdText, myConnection)

        If prams IsNot Nothing Then
            For Each parameter As SqlParameter In prams
                da.SelectCommand.Parameters.Add(parameter)
            Next
        End If

        da.SelectCommand.Parameters.Add(New SqlParameter(RETURNVALUE, SqlDbType.Int, 4, ParameterDirection.ReturnValue, False, 0, _
         0, String.Empty, DataRowVersion.[Default], Nothing))

        Return da
    End Function

    Public Function CreateParam(ByVal ParamName As String, ByVal DbType As SqlDbType, ByVal Size As Int32, ByVal Direction As ParameterDirection, ByVal Value As Object) As SqlParameter
        Dim param As SqlParameter

        If Size > 0 Then
            param = New SqlParameter(ParamName, DbType, Size)
        Else
            param = New SqlParameter(ParamName, DbType)
        End If

        param.Direction = Direction
        If Not (Direction = ParameterDirection.Output AndAlso Value Is Nothing) Then
            param.Value = Value
        End If

        Return param
    End Function

    Public Function CreateInParam(ByVal ParamName As String, ByVal Value As Object, ByVal DbType As SqlDbType, ByVal Size As Integer) As SqlParameter
        Return CreateParam(ParamName, DbType, Size, ParameterDirection.Input, Value)
    End Function

    Public Function CreateOutParam(ByVal ParamName As String, ByVal DbType As SqlDbType, ByVal Size As Integer) As SqlParameter
        Return CreateParam(ParamName, DbType, Size, ParameterDirection.Output, Nothing)
    End Function

    Public Function CreateReturnParam(ByVal ParamName As String, ByVal DbType As SqlDbType, ByVal Size As Integer) As SqlParameter
        Return CreateParam(ParamName, DbType, Size, ParameterDirection.ReturnValue, Nothing)
    End Function
End Class
